using System;
using System.Collections.Generic;
using System.Text;
using System.Data.OleDb;
using XProject.Entity;

namespace XProject.DAO
{
    class ScheduleDAO:DBAccess
    {
        #region sqlcommand
        string SQL_SELECT_ID = "select max(ScheduleID) from [Schedule]";
        string SQL_SELECT_BY_ACCOUNT = "select * from [Schedule] where AccountID=@accountID";
        string SQL_INSERT = "insert into [Schedule](AccountID, StartTime,EndTime, Content, Finished, [Note]) values(@accountID, @startTime, @endTime, @content,@finished, @note)";
        string SQL_UPDATE = "update [Schedule] set AccountID=@accountID, StartTime=@startTime, EndTime=@endTime, Content=@content,Finished=@finished, [Note]=@note where ScheduleID=@scheduleID";
        string SQL_DELETE = "delete from Schedule where ScheduleID=@scheduleID";
        #endregion
        #region fields
        string message;
        #endregion
        #region properties
        public string Message
        {
            get { return message; }
        }
        #endregion
        #region methods
        public IList<ScheduleItem> GetAllSchedule(int accountID)
        {
            IList<ScheduleItem> list = new List<ScheduleItem>();
            message = "";
            try
            {
                OleDbCommand command = new OleDbCommand(SQL_SELECT_BY_ACCOUNT, conn);
            
                command.Parameters.Add("@accountID", OleDbType.Integer).Value = accountID;
                if (Connect())
                {
                    OleDbDataReader dr = command.ExecuteReader();
                    while (dr.Read())
                    {
                        ScheduleItem schedule = new ScheduleItem();
                        schedule.ScheduleID = dr.GetInt32(0);
                        schedule.Account.AccountID = dr.GetInt32(1);
                        schedule.StartTime = dr.GetDateTime(2);
                        schedule.EndTime = dr.GetDateTime(3);                      
                        schedule.Content = dr.GetString(4);                  
                        schedule.Finished = dr.GetBoolean(5);
                        schedule.Note = dr.GetString(6);
                        list.Add(schedule);
                    }
                    Disconnect();
                }
            }
            catch (OleDbException ex)
            {
                message = ex.Message;
            }
            catch (Exception ex)
            {
                message = ex.Message;
            }
            return list;

        }
        public int Insert(ScheduleItem schedule)
        {
            int effectedRows = 0;
            try
            {
                OleDbCommand command = new OleDbCommand(SQL_INSERT, conn);
                command.Parameters.Add("@accountId", OleDbType.Integer).Value = schedule.Account.AccountID;
                command.Parameters.Add("@startTime", OleDbType.Date).Value = schedule.StartTime;
                command.Parameters.Add("@endTime", OleDbType.Date).Value = schedule.EndTime;
                command.Parameters.Add("@content", OleDbType.VarChar).Value = schedule.Content;
               
                command.Parameters.Add("@finished", OleDbType.Boolean).Value = schedule.Finished;
                command.Parameters.Add("@note", OleDbType.VarChar).Value = schedule.Note;
                if (Connect())
                {
                    effectedRows = command.ExecuteNonQuery();
                    command.CommandText = SQL_SELECT_ID;
                    schedule.ScheduleID = int.Parse(command.ExecuteScalar().ToString());
                    Disconnect();
                }
            }
            catch (OleDbException ex)
            {
                message = ex.Message;
            }
            catch (Exception ex) { message = ex.Message; }
            return effectedRows;
        }
        public int Update(ScheduleItem schedule)
        {
            int effectedRows = 0;
            try
            {
                OleDbCommand command = new OleDbCommand(SQL_UPDATE, conn);
                command.Parameters.Add("@accountId", OleDbType.Integer).Value = schedule.Account.AccountID;
                command.Parameters.Add("@startTime", OleDbType.Date).Value = schedule.StartTime;
                command.Parameters.Add("@endTime", OleDbType.Date).Value = schedule.EndTime;
                command.Parameters.Add("@content", OleDbType.VarChar).Value = schedule.Content;
               
                command.Parameters.Add("@finished", OleDbType.Boolean).Value = schedule.Finished;
                command.Parameters.Add("@note", OleDbType.VarChar).Value = schedule.Note;
                command.Parameters.Add("@scheduleID",OleDbType.Integer).Value=schedule.ScheduleID;
                if (Connect())
                {
                    effectedRows = command.ExecuteNonQuery();
                    Disconnect();
                }
            }
            catch (OleDbException ex)
            {
                message = ex.Message;
            }
            catch (Exception ex) { message = ex.Message; }
            return effectedRows;
        }
        public int Delete(ScheduleItem schedule)
        {
            int effectedRows = 0;
            try
            {
                OleDbCommand command = new OleDbCommand(SQL_DELETE, conn);                
                command.Parameters.Add("@scheduleID", OleDbType.Integer).Value = schedule.ScheduleID;
                if (Connect())
                {
                    effectedRows = command.ExecuteNonQuery();
                    Disconnect();
                }
            }
            catch (OleDbException ex)
            {
                message = ex.Message;
            }
            catch (Exception ex) { message = ex.Message; }
            return effectedRows;
        }   
        #endregion
    }
}
